#!/usr/bin/env python3
"""
Safe Database Migration Script
Handles database views and dependencies properly
"""

import sqlite3
import sys
from pathlib import Path

def main():
    db_path = Path(__file__).parent.parent / "data" / "uma_insight.db"

    if not db_path.exists():
        print(f"❌ Database not found: {db_path}")
        sys.exit(1)

    print("🚀 Starting safe database migration...")

    try:
        conn = sqlite3.connect(str(db_path))
        cursor = conn.cursor()

        # Check if directory_id already exists in metrics table
        cursor.execute("PRAGMA table_info(metrics)")
        columns = [row[1] for row in cursor.fetchall()]

        if 'directory_id' in columns:
            print("✅ Migration already completed - directory_id exists in metrics table")

            # Verify the migration worked correctly
            cursor.execute("SELECT COUNT(*) FROM metrics WHERE directory_id IS NOT NULL")
            count = cursor.fetchone()[0]
            print(f"📊 Metrics with directory_id: {count}")

            # Test query
            cursor.execute("""
                SELECT COUNT(*) FROM metrics m
                JOIN directories d ON m.directory_id = d.id
                WHERE d.path = 'tools/metrics/histograms/metadata/accessibility'
            """)
            accessibility_count = cursor.fetchone()[0]
            print(f"📁 Accessibility directory metrics: {accessibility_count}")

            conn.close()
            return

        # Step 1: Create directories_v2 with improved structure
        print("\n📋 Step 1: Creating directories_v2 table...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS directories_v2 (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                path TEXT NOT NULL UNIQUE,
                name TEXT NOT NULL,
                parent_id INTEGER,
                level INTEGER DEFAULT 0,
                team_id TEXT,
                assignment_confidence INTEGER DEFAULT 0,
                assigned_at DATETIME,
                assignment_method TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (parent_id) REFERENCES directories_v2(id) ON DELETE SET NULL,
                FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE SET NULL
            )
        """)

        # Step 2: Extract and build directory hierarchy
        print("\n🌳 Step 2: Building directory hierarchy...")

        # Get all file paths and extract directories
        cursor.execute("""
            SELECT DISTINCT file_path FROM metrics
            WHERE file_path IS NOT NULL AND file_path != ''
            ORDER BY file_path
        """)
        file_paths = [row[0] for row in cursor.fetchall()]

        # Extract all unique directory paths
        all_dirs = set()
        for file_path in file_paths:
            parts = file_path.split('/')
            for i in range(len(parts) - 1):  # Exclude filename
                dir_path = '/'.join(parts[:i+1])
                all_dirs.add(dir_path)

        print(f"   Found {len(all_dirs)} unique directories")

        # Sort directories to ensure parents are created first
        sorted_dirs = sorted(all_dirs, key=lambda x: (x.count('/'), x))

        # Insert directories
        dir_map = {}
        for dir_path in sorted_dirs:
            parts = dir_path.split('/')
            dir_name = parts[-1]

            # Find parent
            parent_path = '/'.join(parts[:-1]) if len(parts) > 1 else None
            parent_id = dir_map.get(parent_path) if parent_path else None
            level = parts.count('/') - 1

            cursor.execute("""
                INSERT OR IGNORE INTO directories_v2 (path, name, parent_id, level)
                VALUES (?, ?, ?, ?)
            """, (dir_path, dir_name, parent_id, level))

            if dir_path not in dir_map:
                cursor.execute("SELECT last_insert_rowid()")
                dir_map[dir_path] = cursor.fetchone()[0]

        print(f"   ✅ Created {len(dir_map)} directories")

        # Step 3: Add columns to metrics table
        print("\n📝 Step 3: Adding columns to metrics table...")

        try:
            cursor.execute("ALTER TABLE metrics ADD COLUMN directory_id INTEGER")
            print("   ✅ Added directory_id column")
        except sqlite3.OperationalError as e:
            if "duplicate column name" in str(e):
                print("   ⚠️ directory_id column already exists")
            else:
                raise

        try:
            cursor.execute("ALTER TABLE metrics ADD COLUMN filename TEXT")
            print("   ✅ Added filename column")
        except sqlite3.OperationalError as e:
            if "duplicate column name" in str(e):
                print("   ⚠️ filename column already exists")
            else:
                raise

        # Step 4: Update metrics with directory_id and filename
        print("\n🔄 Step 4: Updating metrics...")

        cursor.execute("SELECT id, file_path FROM metrics WHERE file_path IS NOT NULL")
        metrics = cursor.fetchall()

        updated = 0
        for metric_id, file_path in metrics:
            parts = file_path.split('/')
            if len(parts) >= 2:
                directory_path = '/'.join(parts[:-1])
                filename = parts[-1]

                if directory_path in dir_map:
                    cursor.execute("""
                        UPDATE metrics
                        SET directory_id = ?, filename = ?
                        WHERE id = ?
                    """, (dir_map[directory_path], filename, metric_id))
                    updated += 1

        print(f"   ✅ Updated {updated} metrics")

        # Step 5: Replace directories table
        print("\n🔄 Step 5: Replacing directories table...")
        cursor.execute("DROP TABLE IF EXISTS directories_old")
        cursor.execute("ALTER TABLE directories RENAME TO directories_old")
        cursor.execute("ALTER TABLE directories_v2 RENAME TO directories")

        # Step 6: Create indexes
        print("\n📊 Step 6: Creating indexes...")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_directories_parent_id ON directories(parent_id)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_directories_path ON directories(path)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_metrics_directory_id ON metrics(directory_id)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_metrics_analysis_status ON metrics(analysis_status)")

        # Step 7: Verify migration
        print("\n✅ Step 7: Verifying migration...")

        cursor.execute("SELECT COUNT(*) FROM metrics")
        metrics_count = cursor.fetchone()[0]

        cursor.execute("SELECT COUNT(*) FROM metrics WHERE directory_id IS NOT NULL")
        valid_count = cursor.fetchone()[0]

        cursor.execute("SELECT COUNT(*) FROM directories")
        directories_count = cursor.fetchone()[0]

        print(f"   Total metrics: {metrics_count}")
        print(f"   Metrics with directory_id: {valid_count}")
        print(f"   Total directories: {directories_count}")

        # Test specific directory
        cursor.execute("""
            SELECT d.path, COUNT(m.id) as count
            FROM directories d
            LEFT JOIN metrics m ON d.id = m.directory_id
            WHERE d.path = 'tools/metrics/histograms/metadata/accessibility'
            GROUP BY d.id, d.path
        """)
        result = cursor.fetchone()
        if result:
            print(f"   ✅ Accessibility directory: {result[1]} metrics")

        # Clean up
        cursor.execute("DROP TABLE directories_old")

        conn.commit()
        print("\n🎉 Migration completed successfully!")

        conn.close()

    except Exception as e:
        print(f"\n❌ Migration failed: {e}")
        conn.rollback()
        conn.close()
        sys.exit(1)

if __name__ == "__main__":
    main()